﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Odbc;
using System.Globalization;

namespace RB.Developer.Tools
{
    /// <summary>
    /// UserControlDmlGenerate.xaml 的交互逻辑
    /// </summary>
    public partial class UserControlDmlGenerate : UserControl
    {
        private static readonly DateTime MinDateTimeValue = DateTime.ParseExact("1753-01-01", "yyyy-MM-dd", CultureInfo.InvariantCulture);

        public UserControlDmlGenerate()
        {
            InitializeComponent();
        }

        private void btnGenerate_Click(object sender, RoutedEventArgs e)
        {
            DataTable schema = GetTableSchema(cmbTable.Text);
            DataTable table = GetTableData(cmbTable.Text, txtFilter.Text);

            string dml = "";

            if (rdoUpdate.IsChecked ?? false)
            {
                dml = BuildUpdate(cmbTable.Text, schema, table);
            }
            else
            {
                dml = BuildInsert(cmbTable.Text, schema, table);
            }

            txtDml.Text = dml;
        }

        private DbConnection GetConnection()
        {
            DbConnection conn = null;
            if (!string.IsNullOrEmpty(txtConnStr.Text))
            {
                string connStr = txtConnStr.Text;
                string connectType = ((ComboBoxItem)cmbConnectType.SelectedValue).Content.ToString();

                if (connectType == "SQL Server")
                {
                    conn = new SqlConnection(connStr);
                }
                else if (connectType == "OLE")
                {
                    conn = new OleDbConnection(connStr);
                }
                else if (connectType == "ODBC")
                {
                    conn = new OdbcConnection(connStr);
                }
            }
            return conn;
        }

        private void btnConnect_Click(object sender, RoutedEventArgs e)
        {
            if (!string.IsNullOrEmpty(txtConnStr.Text))
            {
                string connStr = txtConnStr.Text;
                string connectType = ((ComboBoxItem)cmbConnectType.SelectedValue).Content.ToString();

                DbConnection conn = GetConnection();

                if (conn != null)
                {
                    try
                    {
                        conn.Open();
                        var schema = conn.GetSchema("Tables");
                        var schemaView = new DataView(schema);
                        schemaView.Sort = "TABLE_TYPE";

                        cmbTable.Items.Clear();
                        foreach (DataRowView row in schemaView)
                        {
                            //cmbView.Items.Add(row["TABLE_NAME"]);
                            if ((string)row["TABLE_TYPE"] != "VIEW")
                                cmbTable.Items.Add(row["TABLE_NAME"]);
                        }
                    }
                    catch
                    {
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }

        private void txtFilter_TextChanged(object sender, TextChangedEventArgs e)
        {

        }

        private string BuildInsert(string tableName, DataTable schema, DataTable table)
        {
            StringBuilder strb = new StringBuilder();
            string header = "INSERT INTO " + tableName + "(";
            foreach (DataColumn col in table.Columns)
            {
                header += col.ColumnName + ",";
            }
            foreach (DataRow row in table.Rows)
            {
                strb.Append(header);
                strb.Length--;
                strb.AppendLine(")");
                strb.Append(" VALUES(");
                foreach (DataColumn col in table.Columns)
                {
                    strb.Append(GetConstant(row[col]));
                    strb.Append(",");
                }
                strb.Length--;
                strb.AppendLine(");");
            }
            return strb.ToString();
        }

        private List<string> GetPrimaryKey(DataTable schema)
        {
            List<string> result = new List<string>();
            foreach (DataRow row in schema.Rows)
            {
                if (!row.IsNull(SchemaTableColumn.IsKey) && (bool)row[SchemaTableColumn.IsKey])
                {
                    result.Add((string)row[SchemaTableColumn.ColumnName]);
                }
            }
            return result;
        }

        private string BuildUpdate(string tableName, DataTable schema, DataTable table)
        {
            StringBuilder strb = new StringBuilder();
            string header = "UPDATE " + tableName + " SET ";

            List<string> pkeys = GetPrimaryKey(schema);
            foreach (DataRow row in table.Rows)
            {
                strb.Append(header);
                foreach (ListBoxItem item in listBoxFields.SelectedItems)
                {
                    string columnName = item.Content.ToString();
                    if (!pkeys.Contains(columnName))
                    {
                        strb.Append(columnName);
                        strb.Append("=");
                        strb.Append(GetConstant(row[columnName]));
                        strb.Append(",");
                    }
                }

                strb.Length--;
                if (pkeys.Count > 0)
                {
                    strb.AppendLine();
                    strb.Append(" WHERE ");
                    foreach (string key in pkeys)
                    {
                        strb.Append(key);
                        strb.Append("=");
                        strb.Append(GetConstant(row[key]));
                        strb.Append(" AND ");
                    }
                    strb.Length -= " AND ".Length;
                }
                strb.AppendLine();
            }
            return strb.ToString();
        }

        private DataTable GetTableData(string tableName, string filter)
        {
            DataSet ds = new DataSet();

            string sql = "SELECT * FROM " + tableName;
            if (!string.IsNullOrWhiteSpace(filter))
                sql += " WHERE " + filter;

            DbConnection conn = null;

            try
            {
                conn = GetConnection();
                conn.Open();
                DbCommand cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                DbDataAdapter adp = GetDataAdapter(cmd);
                adp.Fill(ds);
            }
            catch (DbException)
            {
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return ds.Tables[0];
        }

        private DbDataAdapter GetDataAdapter(DbCommand cmd)
        {
            DbDataAdapter adp = null;
            if (!string.IsNullOrEmpty(txtConnStr.Text))
            {
                string connStr = txtConnStr.Text;
                string connectType = ((ComboBoxItem)cmbConnectType.SelectedValue).Content.ToString();

                if (connectType == "SQL Server")
                {
                    adp = new SqlDataAdapter((SqlCommand)cmd);
                }
                else if (connectType == "OLE")
                {
                    adp = new OleDbDataAdapter((OleDbCommand)cmd);
                }
                else if (connectType == "ODBC")
                {
                    adp = new OdbcDataAdapter((OdbcCommand)cmd);
                }
            }
            return adp;
        }

        private DataTable GetTableSchema(string tableName)
        {
            DataTable schema = null;
            DbConnection conn = null;
            DbDataReader reader = null;

            try
            {
                conn = GetConnection();
                conn.Open();
                DbCommand cmd = conn.CreateCommand();

                cmd.CommandText = "SELECT * FROM " + tableName;
                reader = cmd.ExecuteReader(CommandBehavior.KeyInfo);
                schema = reader.GetSchemaTable();
            }
            catch (DbException)
            {
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return schema;
        }

        public string GetConstant(object value)
        {
            if (value == null)
                return "NULL";
            Type t = value.GetType();
            string valueStr = "";
            if (t == typeof(string))
                valueStr = EncodeString(value as string, true);
            else if (t == typeof(DateTime))
                valueStr = EncodeDateTime((DateTime)value);
            else if (t == typeof(bool))
            {
                valueStr = (bool)value ? "1" : "0";
            }
            else if (t == typeof(DBNull))
                valueStr = "NULL";
            else if (t == typeof(Guid))
                valueStr = "'" + ((Guid)value).ToString() + "'";
            else if (t.IsEnum)
            {
                valueStr = ((int)value).ToString(CultureInfo.InvariantCulture);
            }
            else if (t.IsArray)
            {
                // 处理变量是数组的情况
                var array = (Array)value;
                if (array.Length > 0)
                {
                    valueStr = "(";
                    valueStr += GetConstant(array.GetValue(0));
                    for (int i = 1; i < array.Length; i++)
                    {
                        valueStr += ",";
                        valueStr += GetConstant(array.GetValue(i));
                    }
                    valueStr += ")";
                }
            }
            else
                valueStr = value.ToString();
            //if (valueStr == "''")
            //    return "NULL";
            return valueStr;
        }

        private static string EncodeString(string fieldValue, bool quote)
        {
            fieldValue = fieldValue.Replace("'", "''");
            if (quote)
                fieldValue = "'" + fieldValue + "'";
            return fieldValue;
        }

        private static string EncodeDateTime(DateTime dateTime)
        {
            if (dateTime == DateTime.MinValue)
            {
                return "NULL";
            }
            else
            {
                if (dateTime.Date < MinDateTimeValue)
                {
                    return "'" + dateTime.ToString("HH:mm:ss.fff", CultureInfo.InvariantCulture) + "'";
                }
                else if (dateTime.TimeOfDay == TimeSpan.Zero)
                {
                    return "'" + dateTime.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture) + "'";
                }
                return "'" + dateTime.ToString("yyyy-MM-dd HH:mm:ss.fff", CultureInfo.InvariantCulture) + "'";
            }
        }

        private void cmbTable_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            
        }

        private void cmbTable_LostFocus(object sender, RoutedEventArgs e)
        {
            if (!string.IsNullOrWhiteSpace(cmbTable.Text))
            {
                DataTable schema = GetTableSchema(cmbTable.Text);
                if (schema != null)
                {
                    foreach (DataRow row in schema.Rows)
                    {
                        ListBoxItem item = new ListBoxItem()
                        {
                            Content = row["ColumnName"]
                        };
                        listBoxFields.Items.Add(item);
                    }
                }
            }
        }
    }
}
